﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using DataBaseConnection;
using System.Collections;

namespace DataEntity
{
    public class IEntity
    {
        public IEntity()
        {
            
        }

        /// <summary>
        /// 将成员变量转换成Null值
        /// </summary>
        /// <param name="Nullable">false:不转换 true:转换</param>
        public IEntity(bool Nullable)
        {
            if (!Nullable) { return; }

            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                try
                {
                    fi.SetValue(this, null);
                }
                catch { }
            }
        }

        #region 私有成员
        private bool _IsEmpty = true;
        /// <summary>
        /// 是否空值
        /// </summary>
        public bool IsEmpty
        {
            set { _IsEmpty = value; }
            get { return _IsEmpty; }
        }

        protected bool IsRollBack = false;//是否参与回滚事务
        protected PConnOrder co = null;//该实例对象使用的数据库操作对象
        /// <summary>
        /// 设置统一回滚事务操作对象
        /// </summary>
        public PConnOrder SetConnOrder
        {
            set { 
                co = value;
                IsRollBack = true;
            }
        }
        #endregion

        #region 公共方法
        /// <summary>        
        /// 数据表转换成员值
        /// </summary>
        public void SetValues(DataRow drReceive)
        {
            // 如果表格的行数为0，则表示该实体没有初始化，IsEmpty=true
            if (drReceive == null) { IsEmpty = true; return; }

            // 枚举实体类属性,把接受的datatable中的数据赋給根据类属性架构的datatable
            foreach (DataColumn column in drReceive.Table.Columns)
            {
                string columnName = column.ColumnName;
                FieldInfo property = this.GetType().GetField(columnName);

                if (property != null && drReceive[columnName] != DBNull.Value)
                {
                    try
                    {
                        //property.SetValue(this, Convert.ChangeType(drReceive[columnName], property.ReflectedType));
                        property.SetValue(this, drReceive[columnName]);
                    }
                    catch { }
                }
            }
            IsEmpty = false;
        }

        /// <summary>
        /// 表的第一行转换
        /// </summary>
        /// <param name="dtReceive">表</param>
        public void SetValues(DataTable dtReceive)
        {
            if (dtReceive == null || dtReceive.Rows.Count == 0) { IsEmpty = true; return; }
            SetValues(dtReceive.Rows[0]);
        }

        /// <summary>
        /// 获取对象的表结构
        /// </summary>
        /// <returns>有结构的空表</returns>
        public DataTable GetDataTableSchema()
        {
            DataTable dt = new DataTable(this.GetType().Name);

            //循环对象的公共成员
            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                try
                {
                    DataColumn dc = new DataColumn(fi.Name, fi.FieldType);
                    dt.Columns.Add(dc);
                }
                catch { }
            }
            return dt.Clone();
        }
        /// <summary>
        /// 用一个分隔符将一个数组组合成字符串
        /// </summary>
        /// <param name="ArrayObj">数组</param>
        /// <param name="compart">分隔符</param>
        /// <returns>如：a,b,c</returns>
        protected string UniteArray(string[] ArrayObj, string compart)
        {
            if (ArrayObj == null && ArrayObj.Length == 0) { return string.Empty; }
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ArrayObj.Length; i++)
            {
                sb.Append(ArrayObj[i]).Append((i == ArrayObj.Length - 1) ? string.Empty : compart);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取第一个主键或递增
        /// </summary>        
        public FieldInfo GetTopKey()
        {
            FieldInfo refi = null;
            //循环对象的公共成员
            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                //字段存在 and 不为递增

                if (fi != null)
                {
                    //优先为主键
                    if (DataEntity.Common.ExistsPrimaryKey(fi)) { return fi; }
                    //暂存递增
                    if (DataEntity.Common.ExistsAutoKey(fi)) { refi = fi; }
                }
            }
            return refi;
        }
        #endregion

        #region 插入操作
        /// <summary>
        /// 插入一条记录
        /// </summary>        
        /// <returns>影响行数</returns>
        public int Insert()
        {            
            List<string> sql_field = new List<string>();
            List<string> sql_value = new List<string>();
            List<IDataParameter> paras = new List<IDataParameter>();

            if (co == null) { co = CSelectDataBase.GetDataBase(); }
             
            StringBuilder sql = new StringBuilder();
            //循环对象的公共成员
            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                //字段存在 and 不为递增
                if (fi != null && fi.GetValue(this) != null && !DataEntity.Common.ExistsAutoKey(fi))
                {
                    sql_field.Add(fi.Name);
                    sql_value.Add("@" + fi.Name);
                    paras.Add(co.GetNewParameter("@" + fi.Name, fi.GetValue(this)));                    
                }
            }
            //组合sql插入语句
            string template = @"INSERT INTO [{0}] ({1}) VALUES ({2})";
            sql.Append(string.Format(template,
                this.GetType().Name,
                UniteArray(sql_field.ToArray(), ","),
                UniteArray(sql_value.ToArray(), ",")
                ));
            //执行语句
            int result = co.ExecuteNonQuery(sql.ToString(), paras.ToArray(), CommandType.Text);

            if (!IsRollBack) { co.ConnectionClose(); }

            return result;
        }

        /// <summary>
        /// 插入一条记录 并 返回新产生的自动编号
        /// </summary>
        public object Insert_GetNewID()
        {
            List<string> sql_field = new List<string>();
            List<string> sql_value = new List<string>();
            List<IDataParameter> paras = new List<IDataParameter>();

            if (co == null) { co = CSelectDataBase.GetDataBase(); }
            StringBuilder sql = new StringBuilder();
            //循环对象的公共成员
            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                //字段存在 and 不为递增
                if (fi != null && fi.GetValue(this) != null && !DataEntity.Common.ExistsAutoKey(fi))
                {
                    sql_field.Add(fi.Name);
                    sql_value.Add("@" + fi.Name);
                    paras.Add(co.GetNewParameter("@" + fi.Name, fi.GetValue(this)));
                }
            }
            //组合sql插入语句
            string tableName = this.GetType().Name;
            string template = @"INSERT INTO [{0}] ({1}) VALUES ({2})";
            sql.Append(string.Format(template,
                tableName,
                UniteArray(sql_field.ToArray(), ","),
                UniteArray(sql_value.ToArray(), ",")
                ));
            
            //执行语句
            object result = 0;

            switch (co.CurrentBaseType)
            {
                case DataBaseType.ACCESS:
                    int Staute = co.ExecuteNonQuery(sql.ToString(), paras.ToArray(), CommandType.Text);
                    if (Staute > 0)
                    {
                        result = co.ExecuteScalar("SELECT @@identity", paras.ToArray(), CommandType.Text);
                    }
                    break;
                case DataBaseType.MSSQL:
                    sql.Append("; SELECT IDENT_CURRENT('").Append(tableName).Append("');");
                    result = co.ExecuteScalar(sql.ToString(), paras.ToArray(), CommandType.Text);
                    break;
            }

            if (!IsRollBack) { co.ConnectionClose(); }
            return result;
        }
        #endregion

        #region 更新操作

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="keyValue">主键值</param>        
        public int Update(object keyValue)
        {
            //获取top1 主键(优先)或递增
            FieldInfo fi = GetTopKey();
            return Update(fi.Name + " = @" + fi.Name, new string[]{ "@" + fi.Name }, new object[]{ keyValue });
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="whereSql">条件sql语句</param>
        /// <param name="whereKeys">条件变量名</param>
        /// <param name="whereValues">条件变量值</param>
        /// <returns></returns>
        public int Update(string whereSql, string[] whereKeys, object[] whereValues)
        {
            if (co == null) { co = CSelectDataBase.GetDataBase(); }

            List<string> sql_set = new List<string>();
            List<IDataParameter> paras = new List<IDataParameter>();
            FieldInfo fi_top = GetTopKey();

            StringBuilder sql = new StringBuilder();
            //循环对象的公共成员
            foreach (FieldInfo fi in this.GetType().GetFields())
            {
                if (fi != null && fi.GetValue(this) != null && !DataEntity.Common.ExistsAutoKey(fi) && !DataEntity.Common.ExistsPrimaryKey(fi))
                {
                    sql_set.Add(fi.Name + " = @" + fi.Name);
                    paras.Add(co.GetNewParameter("@" + fi.Name, fi.GetValue(this)));
                }
            }
            //加载条件变量
            if (whereKeys != null && whereValues != null)
            {
                for (int i = 0; i < whereKeys.Length; i++)
                {
                    paras.Add(co.GetNewParameter(whereKeys[i], whereValues[i]));
                }
            }

            //组合sql插入语句
            string template = @"UPDATE [{0}] SET {1}";
            sql.Append(string.Format(template,
                this.GetType().Name,
                UniteArray(sql_set.ToArray(), ",")
                ));
            //组合where条件
            sql.Append(" WHERE ").Append(whereSql);
           
            //执行语句
            int result = co.ExecuteNonQuery(sql.ToString(), paras.ToArray(), CommandType.Text);
            if (!IsRollBack) { co.ConnectionClose(); }
            return result;
        }
        #endregion

        #region 删除操作
        /// <summary>
        /// 删除一条记录(主键或递增编号)
        /// </summary>
        /// <returns>影响行数</returns>
        public int Delete(object keyValue)
        {
            //获取top1 主键(优先)或递增
            FieldInfo fi = GetTopKey();
            string ws = string.Empty;
            string[] wks = null;
            object[] wvs = null;

            if (fi != null)
            {
                ws = fi.Name + " = @" + fi.Name;
                wks = new string[] { "@" + fi.Name };
                wvs = new object[] { (keyValue == null) ? fi.GetValue(this) : keyValue };
            }
            return Delete(ws, wks, wvs);
        }
        /// <summary>
        /// 删除多条记录(自定义条件)
        /// </summary>
        /// <param name="whereSql">sql条件语句</param>
        /// <param name="whereKeys">sql条件里的变量</param>
        /// <param name="whereValues">条件变量对应的值</param>
        /// <returns>影响行数</returns>
        public int Delete(string whereSql, string[] whereKeys, object[] whereValues)
        {
            if (whereSql == string.Empty) { throw new Exception("删除操作：条件不能为空"); }

            if (co == null) { co = CSelectDataBase.GetDataBase(); }
            List<IDataParameter> paras = new List<IDataParameter>();
            if (whereKeys != null && whereValues != null)
            {
                for (int i = 0; i < whereKeys.Length; i++)
                {
                    paras.Add(co.GetNewParameter(whereKeys[i], whereValues[i]));
                }
            }

            StringBuilder sql = new StringBuilder();
            
            //组合sql删除语句
            string template = @"DELETE FROM [{0}]";
            sql.Append(string.Format(template,
                this.GetType().Name                
                ));
            //组合where条件
            sql.Append(" WHERE ").Append(whereSql);
            //执行语句
            int result = co.ExecuteNonQuery(sql.ToString(), paras.ToArray(), CommandType.Text);
            if (!IsRollBack) { co.ConnectionClose(); }
            return result;
        }
        #endregion

        #region 选择操作
        /// <summary>
        /// 选择一条记录(主键或递增编号)
        /// </summary>        
        public void Select()
        {            
            Select(null);
        }
        /// <summary>
        /// 选择一条记录(主键或递增编号)
        /// </summary>        
        public void Select(object keyValue)
        {
            if (keyValue == null) { Select(null, null, null); return; }
            //获取top1 主键(优先)或递增
            FieldInfo fi = GetTopKey();
            string ws = string.Empty;
            string[] wks = null;
            object[] wvs = null;

            if (fi != null)
            {
                ws = fi.Name + " = @" + fi.Name;
                wks = new string[] { "@" + fi.Name };
                wvs = new object[] { keyValue };
            }
            Select(ws, wks, wvs);
        }
        /// <summary>
        /// 选择一条记录(自定义条件)
        /// </summary>
        /// <param name="whereSql">sql条件语句</param>
        /// <param name="whereKeys">sql条件里的变量</param>
        /// <param name="whereValues">条件变量对应的值</param>        
        public void Select(string whereSql, string[] whereKeys, object[] whereValues)
        {
            DataTable result = SelectDataTable(whereSql, whereKeys, whereValues, 1);

            if (result != null && result.Rows.Count != 0)
            {
                SetValues(result.Rows[0]);
            }
            else { IsEmpty = true; }
        }
        /// <summary>
        /// 选择DataTable表
        /// </summary>
        /// <param name="whereSql">sql条件语句</param>
        /// <param name="whereKeys">sql条件里的变量</param>
        /// <param name="whereValues">条件变量对应的值</param> 
        /// <returns>DataTable</returns>
        public DataTable SelectDataTable(string whereSql, string[] whereKeys, object[] whereValues)
        {
            return SelectDataTable(whereSql, whereKeys, whereValues, 0);
        }
        /// <summary>
        /// 选择DataTable表
        /// </summary>
        /// <param name="whereSql">sql条件语句</param>
        /// <param name="whereKeys">sql条件里的变量</param>
        /// <param name="whereValues">条件变量对应的值</param> 
        /// <param name="topCount">前几条</param>
        /// <returns>DataTable</returns>
        public DataTable SelectDataTable(string whereSql, string[] whereKeys, object[] whereValues, int topCount)
        {
            if (co == null) { co = CSelectDataBase.GetDataBase(); }
            List<IDataParameter> paras = new List<IDataParameter>();
            if (whereKeys != null && whereValues != null)
            {
                for (int i = 0; i < whereKeys.Length; i++)
                {
                    paras.Add(co.GetNewParameter(whereKeys[i], whereValues[i]));
                }
            }

            StringBuilder sql = new StringBuilder();

            //组合sql删除语句
            string template = @"SELECT {1} * FROM [{0}]";
            sql.Append(string.Format(template,
                this.GetType().Name,
                (topCount == 0) ? string.Empty : "TOP " + topCount.ToString()
                ));
            //组合where条件
            if (whereSql != null && whereSql != string.Empty)
            {
                sql.Append(" WHERE ").Append(whereSql);
            }
            //执行语句
            DataTable result = co.ExecuteDataSet(sql.ToString(), paras.ToArray(), CommandType.Text).Tables[0];
            if (!IsRollBack) { co.ConnectionClose(); }
            return result;
        }
        #endregion
    }
}
